USE [ManagerEmployees_WinForm]
GO

CREATE VIEW View_Districts
AS
SELECT		Districts.*, Provinces.NameOfProvince
FROM        Districts INNER JOIN Provinces ON Districts.ProvID = Provinces.ProvID
GO


CREATE VIEW View_Communes
AS
SELECT			Communes.*, Districts.NameOfDist AS Expr1, Provinces.NameOfProvince, Districts.DistID AS Expr2, Provinces.ProvID
FROM			Communes	INNER JOIN Districts ON Communes.DistID = Districts.DistID 
							INNER JOIN Provinces ON Districts.ProvID = Provinces.ProvID
GO



CREATE VIEW View_Ogranizations
AS
SELECT				Organizations.*, Communes.ComID AS Expr1, Communes.NameOfComm, Districts.DistID, Districts.NameOfDist, Provinces.ProvID, Provinces.NameOfProvince
FROM				Communes	INNER JOIN Districts ON Communes.DistID = Districts.DistID 
								INNER JOIN Organizations ON Communes.ComID = Organizations.ComID 
								INNER JOIN Provinces ON Districts.ProvID = Provinces.ProvID
GO


CREATE VIEW View_Departments
AS
SELECT	Communes.ComID AS Expr1, Communes.NameOfComm, Districts.DistID, Districts.NameOfDist, Provinces.ProvID, 
		Provinces.NameOfProvince, Departments.*, Organizations.OrgID AS Expr2, Organizations.NameOfOrgan
FROM	Communes	INNER JOIN Districts ON Communes.DistID = Districts.DistID 
					INNER JOIN Organizations ON Communes.ComID = Organizations.ComID 
					INNER JOIN Provinces ON Districts.ProvID = Provinces.ProvID 
					INNER JOIN Departments ON Organizations.OrgID = Departments.OrgID
GO

CREATE PROC Proc_ViewDepartmentsByOgranID
	@OgranID			INT
AS
BEGIN
SELECT	Communes.ComID AS Expr1, Communes.NameOfComm, Districts.DistID, Districts.NameOfDist, Provinces.ProvID, 
		Provinces.NameOfProvince, Departments.*, Organizations.OrgID AS Expr2, Organizations.NameOfOrgan
FROM	Communes	INNER JOIN Districts ON Communes.DistID = Districts.DistID 
					INNER JOIN Organizations ON Communes.ComID = Organizations.ComID 
					INNER JOIN Provinces ON Districts.ProvID = Provinces.ProvID 
					INNER JOIN Departments ON Organizations.OrgID = Departments.OrgID
WHERE Departments.OrgID = @OgranID
END
GO



CREATE VIEW	View_EmployeesInfoBasicWhenCreateSuccess
AS
SELECT        Departments.NameOfDep, Employees.*, Communes.NameOfComm, Organizations.NameOfOrgan, Communes.ComID, Organizations.OrgID
FROM            Departments INNER JOIN Employees ON Departments.DepID = Employees.DepUsingID 
							INNER JOIN Organizations ON Departments.OrgID = Organizations.OrgID 
							INNER JOIN Communes ON Organizations.ComID = Communes.ComID
GO


CREATE VIEW	View_TopEmployeesInfoBasicWhenCreateSuccess
AS
SELECT        TOP 10 Departments.NameOfDep, Employees.*, Communes.NameOfComm, Organizations.NameOfOrgan, Communes.ComID, Organizations.OrgID
FROM            Departments INNER JOIN Employees ON Departments.DepID = Employees.DepUsingID 
							INNER JOIN Organizations ON Departments.OrgID = Organizations.OrgID 
							INNER JOIN Communes ON Organizations.ComID = Communes.ComID
GO




CREATE VIEW	View_EmployeesRelationsFamilyInfos
AS
SELECT        FamilyRelationships.*, Employees.EmpID AS Expr1, Employees.FirstName, Employees.AvatarUrl, Employees.LastName, Employees.Email, Employees.Telephone, Employees.Gender
FROM            Employees INNER JOIN FamilyRelationships ON Employees.EmpID = FamilyRelationships.EmpID
GO


-- 31/1/2013
CREATE PROC Proc_InfoEmployeeProfileByEmpID
	@EmpID				INT
AS
BEGIN
	SELECT			Profiles.*, Employees.EmpID AS Expr1, Employees.Code, Employees.AvatarUrl, Employees.LastName, Employees.FirstName, Employees.Gender, Employees.DateOfBirth
	FROM			Employees INNER JOIN Profiles ON Employees.EmpID = Profiles.EmpID
	WHERE			Profiles.EmpID = @EmpID
END

GO

CREATE VIEW View_EmployeeQualifications
as
SELECT        EmployeesQualifications.*, Qualifications.NameOfQua
FROM            EmployeesQualifications INNER JOIN
                         Qualifications ON EmployeesQualifications.QuaID = Qualifications.QuaID
GO

CREATE PROC PROC_EmployeeQualifications
as
SELECT        EmployeesQualifications.*, Qualifications.NameOfQua
FROM            EmployeesQualifications INNER JOIN
                         Qualifications ON EmployeesQualifications.QuaID = Qualifications.QuaID
GO